USE SAS
GO

 IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'GetAccountDetails')
	BEGIN
		DROP  Procedure  dbo.GetAccountDetails
	END
GO

CREATE Procedure [dbo].[GetAccountDetails]
	@PKID		varchar(100) = ''
,	@OWNERID	varchar(10) = ''
AS
BEGIN

	declare 
		@sql			varchar(max)
	,	@states			varchar(max)
	,	@parentAccs		varchar(max)
	,	@allUsers		varchar(max)
	,	@accContacts	varchar(max)
	,	@accType		varchar(max)
	,	@verticals		varchar(max)
	,	@resellers		varchar(max)
	,	@refSources		varchar(max)
	,	@relations		varchar(max)
	,	@cities			varchar(max)

	SELECT	@sql = ''
		,	@states			= dbo.GetStatesXml()
		,	@parentAccs		= dbo.GetParentAccountsXml()
		,	@allUsers		= dbo.GetAllUsersXml()
		,	@accContacts	= dbo.GetContactsXml(@PKID)
		,	@accType		= dbo.GetAccountTypesXML()
		,	@verticals		= dbo.GetVerticalsXml()
		,	@resellers		= dbo.GetResellersXml()
		,	@refSources		= dbo.GetRefSourcesXml ()
		,	@relations		= dbo.GetRelationsXML(@PKID)
		,	@cities			= dbo.GetCityXML(@OWNERID)

	;WITH Acc AS (
		SELECT 
			p.PartnerNo			AS AccountNo
		,	p.PartnerName		AS AccountName
		,	p.NoofLocations		AS NoofLocations
		,	p.Website			AS WebSite
		,	p.Vertical			AS VerticalID
		,	p.PartnerGUID		AS AccountGUID	
		,	p.AccountGroup		AS ACCOUNTTYPEID
		,	p.DataOrigin		AS REFERRALSOURCEID
		,	p.IsActive			AS IsActive
		,	p.PartnerDivision	AS PARTNERDIVISIONID
		,	r.ResellerNo		as ResellerNo
		,	r.OtherReseller		as OtherReseller
		FROM TR_PARTNER as p WITH (NOLOCK)
		LEFT OUTER JOIN TR_ACCOUNT as r WITH (NOLOCK)
			ON r.AccountGUID = p.PartnerGUID
		WHERE
			CAST(p.PartnerGUID AS VARCHAR(40)) = ISNULL(@PKID, '')
		UNION
		SELECT TOP 1
			'0'					AS AccountNo
		,	''					AS AccountName
		,	'0'					AS NoofLocations
		,	''					AS WebSite
		,	'0'					AS VerticalID
		,	NEWID()				AS AccountGUID	
		,	'0'					AS ACCOUNTTYPEID
		,	'0'					AS REFERRALSOURCEID
		,	1					AS IsActive
		,	1					AS PARTNERDIVISIONID
		,	0					as ResellerNo
		,	NULL				as OtherReseller
		FROM TR_PARTNER as p WITH (NOLOCK)
		WHERE
			ISNULL(@PKID,'') = ''
	), AccAddress AS (
		SELECT TOP 1
			a.AddressNo
		,	a.Street1
		,	a.Street2
		,	a.CityNo
		,	a.State
		,	a.Zip
		,	a.AddressGUID
		,	p.AccountGUID
		FROM TR_ADDRESS as a WITH (NOLOCK)
		INNER JOIN Acc as p WITH (NOLOCK)
			on p.AccountGUID = a.PartnerGUID
	), AccComm AS (
		SELECT TOP 1
			c.CommunicationID
		,	c.Phone
		,	c.Fax
		,	a.AddressGUID
		FROM TR_COMMUNICATION as c WITH (NOLOCK)
		INNER JOIN AccAddress as a
			on a.AddressGUID = c.AddressGUID
	), AccNotes AS (
		SELECT TOP 1
			n.NoteID
		,	n.NoteText
		,	n.SourceGUID
		FROM TR_NOTE as n with (NOLOCK)
		INNER JOIN Acc as p with (NOLOCK)
			on p.AccountGUID = n.SourceGUID
	), AccDetail AS (
		SELECT 1 AS ID, N'<DATA>' AS Details 
		UNION
		SELECT 2 AS ID, N'<RESPONSE><RESULT>S</RESULT><MESSAGE></MESSAGE></RESPONSE>'
		UNION
		SELECT 2.1 as ID, @parentAccs as Details
		UNION
		SELECT 2.2 as ID, @allUsers as Details
		UNION
		SELECT 2.3 as ID, @accContacts as Details
		UNION
		SELECT 2.4 as ID, @accType as Details
		UNION
		SELECT 2.5 as ID, @states as Details
		UNION
		SELECT 2.6 as ID, @verticals as Details
		UNION
		SELECT 2.7 as ID, @resellers as Details
		UNION
		SELECT 2.8 as ID, @refSources as Details
		UNION
		SELECT 2.9 as ID, @relations as Details
		UNION
		SELECT 2.91 as ID, @cities as Details
		UNION
		SELECT
			3 AS ID, 
		+	'<PKID><![CDATA[' + CAST(p.AccountGUID AS VARCHAR(40)) + ']]></PKID>'
		+	'<ACCOUNTGUID><![CDATA[' + CAST(p.AccountGUID AS VARCHAR(40)) + ']]></ACCOUNTGUID>'
		+	'<ACCOUNTNO><![CDATA[' + ISNULL(CAST(p.AccountNo AS VARCHAR), '0') + ']]></ACCOUNTNO>'
		+	'<ACCOUNTNAME><![CDATA[' + AccountName + ']]></ACCOUNTNAME>'
		+	'<NOOFLOCATIONS><![CDATA['+ ISNULL(CAST(p.NoOfLocations AS VARCHAR),'') +']]></NOOFLOCATIONS>'
		+	'<ACCOUNTTYPEID><![CDATA['+ ISNULL(CAST(p.ACCOUNTTYPEID AS VARCHAR),'0') +']]></ACCOUNTTYPEID>'
		+	'<STREET1><![CDATA[' + ISNULL(Street1,'') + ']]></STREET1>'
		+	'<STREET2><![CDATA[' + ISNULL(Street2,'') + ']]></STREET2>'
		+	'<CITYNO><![CDATA[' + CAST(ISNULL(CityNo,'0') AS VARCHAR) + ']]></CITYNO>'
		+	'<STATE><![CDATA[' + ISNULL([State], '') + ']]></STATE>'
		+	'<ZIP><![CDATA[' + ISNULL(Zip,'') + ']]></ZIP>'
		+	'<PHONE><![CDATA[' + ISNULL(Phone,'') + ']]></PHONE>'
		+	'<FAX><![CDATA[' + ISNULL(Fax,'') + ']]></FAX>'
		+	'<WEBSITE><![CDATA[' + ISNULL(WebSite,'') + ']]></WEBSITE>'
		+	'<VERTICALID><![CDATA['+CAST(p.VerticalID AS VARCHAR)+']]></VERTICALID>'
		+	'<REFERRALSOURCEID><![CDATA['+ ISNULL(CAST(p.REFERRALSOURCEID AS VARCHAR),'0')+']]></REFERRALSOURCEID>'
		+	'<RESELLERID><![CDATA['+ ISNULL(CAST(p.RESELLERNo AS VARCHAR),'0')+']]></RESELLERID>'
		+	'<OTHERRESELLER><![CDATA[' + ISNULL(p.OTHERRESELLER,'') + ']]></OTHERRESELLER>'
		+	'<NOTEID><![CDATA[' + CAST(ISNULL(n.NoteID,0) AS VARCHAR) + ']]></NOTEID>'
		+	'<NOTES><![CDATA[' + ISNULL(n.NOTETEXT,'') + ']]></NOTES>'
		+	'<ISACTIVE><![CDATA[' + (CASE WHEN ISNULL(IsActive,1) = 1 THEN 'X' ELSE '' END) + ']]></ISACTIVE>' 
		+	'<ADDRESSNO><![CDATA[' + ISNULL(CAST(a.AddressNo AS VARCHAR),'0')+ ']]></ADDRESSNO>' 
		+	'<ADDRESSGUID><![CDATA[' + ISNULL(CAST(a.ADDRESSGUID AS VARCHAR(40)),'')+ ']]></ADDRESSGUID>' 
		+	'<COMMUNICATIONID><![CDATA[' + ISNULL(CAST(c.COMMUNICATIONID AS VARCHAR),'0') + ']]></COMMUNICATIONID>' 
		+	'<PARTNERDIVISIONID><![CDATA[' + ISNULL(CAST(p.PARTNERDIVISIONID AS VARCHAR),'0')+ ']]></PARTNERDIVISIONID>' 
					AS Details
		FROM Acc as p
		LEFT OUTER JOIN AccAddress as a
			ON a.AccountGUID = p.AccountGUID
		LEFT OUTER JOIN AccComm as c
			on c.AddressGUID = a.AddressGUID
		LEFT OUTER JOIN AccNotes as n
			on n.SourceGUID = p.AccountGUID
		UNION
		SELECT 4 AS ID, '</DATA>' AS Details 
	)

	SELECT @sql = @sql + Details
	FROM AccDetail
	Order by ID


	select @sql	AS Detail
END
GO
